import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data, addmonth
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_ccynwxxhtop10dyddlx(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    date = datetime.datetime.strptime(date, '%Y-%m-%d')
    last_month = (addmonth(date, -1)).strftime('%Y-%m-%d')
    year_month = '-'.join(last_month.split('-')[:2])
    date = date.strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)

    sql = f'''insert into fridge_biz_result.result_ccynwxxhtop10dyddlx
                (
                select
                    c1.model,
                    if(c2.order_type is null,'',c2.order_type) as order_type,
                    count(distinct c2.objectid) as order_count,
                    max(c1.repair_count) as order_total,
                    substr(p_date,1,7) as time
                from
                (
                    select
                        model,repair_count,time
                    from
                        fridge_biz_result.result_ccynwxxhtop10
                    where
                        time = '{year_month}' 
                ) c1
                left join
                    (
                        select
                            upper(if(t3.model is null,regexp_extract(zzfld000036,'[a-zA-Z0-9]+-[a-zA-Z0-9]+'),t3.model)) as model,
                            t1.objectid,
                            if(zzfld00002ft is null or zzfld00002ft = '','未知',zzfld00002ft) as order_type,
                            COALESCE(
                                TRY(
                                    date(
                                        concat(
                                            t2.name,'-',
                                            substr(t1.zzfld00000b,17,2),'-',
                                            substr(t1.zzfld00000b,19,2)
                                            )
                                        )
                                    ),null
                            ) factory_time,
                            p_date
                        from
                            ods.kyd_ods  t1
                        left join
                            dim.tree_book t2
                        on
                            substr(t1.zzfld00000b,16,1) = t2.code and t2.classify_name = 'sn出厂年份'
                        left join
                            dim.material t3
                        on
                            substr(t1.zzfld00000b,4,7) = t3.material
                        where
                            length(zzfld00000b) =24
                        and
                            date(p_date)>=(date('{date}') - interval '1' month)
                        and
                            date(p_date) < date('{date}')
                        and
                            zzfld000001='QT30'
                    ) c2
                on
                    c1.model = if(c2.model is null,'',c2.model) and c1.time = substr(c2.p_date,1,7)
                    and date(p_date) - interval '12' month < factory_time and date(p_date) >= factory_time
                group by
                    substr(p_date,1,7),c1.model, c2.order_type
                )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_ccynwxxhtop10dyddlx(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

